package com.corx.network.mysqlstorage.repository;

import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

import java.io.IOException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author gx
 * @version 1.0.0
 */
public class JdbcDatabaseInitializer implements DatabaseInitializer {


    private final JdbcTemplate jdbcTemplate;
    private final Boolean overwrite;
    private final String classpath;
    // 排除表集合
    private final Set<String> excludeTables;

    public JdbcDatabaseInitializer(JdbcTemplate jdbcTemplate) {
        this(jdbcTemplate, Collections.emptySet());
    }


    public JdbcDatabaseInitializer(JdbcTemplate jdbcTemplate, Set<String> excludeTables) {
        this.jdbcTemplate = Objects.requireNonNull(jdbcTemplate, "JdbcTemplate is null");
        this.overwrite = false;
        this.classpath = "classpath:/db/schema/*.sql";
        this.excludeTables = excludeTables != null ? excludeTables : Collections.emptySet();
    }


    @Override
    public void init() {
        try {
            if (overwrite) {
                System.out.println("[DB Init] Overwrite mode enabled, dropping tables...");
                deleteExistingTables();
            }
            System.out.println("[DB Init] Creating tables from SQL files...");
            createDeviceTables();
        } catch (Exception e) {
            throw new RuntimeException("[DB Init] Failed to initialize database: " + e.getMessage(), e);
        }
    }


    @Override
    public void createDeviceTables() throws IOException {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resources = resolver.getResources(classpath);

        if (resources.length == 0) {
            System.out.println("[DB Init] No SQL files found under path: " + classpath);
            return;
        }

        Pattern pattern = Pattern.compile("CREATE TABLE IF NOT EXISTS [`\"]?(\\w+)[`\"]?", Pattern.CASE_INSENSITIVE);

        for (Resource resource : resources) {
            // 新增：判断SQL文件是否包含要排除的表
            boolean skip = false;
            try (Scanner scanner = new Scanner(resource.getInputStream())) {
                while (scanner.hasNextLine()) {
                    String line = scanner.nextLine().trim();
                    Matcher matcher = pattern.matcher(line);
                    if (matcher.find()) {
                        String tableName = matcher.group(1);
                        if (excludeTables.contains(tableName)) {
                            System.out.println("[DB Init] Skip creating excluded table: " + tableName + " in file: " + resource.getFilename());
                            skip = true;
                            break;
                        }
                    }
                }
            }
            if (skip) {
                continue;
            }
            System.out.println("[DB Init] Executing SQL script: " + resource.getFilename());
            ResourceDatabasePopulator populator = new ResourceDatabasePopulator(resource);
            populator.execute(Objects.requireNonNull(jdbcTemplate.getDataSource()));
        }
    }

    @Override
    public void deleteExistingTables() {
        try {
            List<String> tableNames = extractTableNamesFromSqlFiles();
            if (tableNames.isEmpty()) {
                System.out.println("[DB Init] No table names found for dropping.");
                return;
            }
            String dropSql = "DROP TABLE IF EXISTS " + String.join(", ", tableNames) + ";";
            jdbcTemplate.execute(dropSql);
            System.out.println("[DB Init] Dropped tables: " + tableNames);
        } catch (Exception e) {
            throw new RuntimeException("[DB Init] Error dropping existing tables: " + e.getMessage(), e);
        }
    }


    private List<String> extractTableNamesFromSqlFiles() throws IOException {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resources = resolver.getResources(classpath);

        Pattern pattern = Pattern.compile("CREATE TABLE IF NOT EXISTS [`\"]?(\\w+)[`\"]?", Pattern.CASE_INSENSITIVE);
        Set<String> tableNames = new LinkedHashSet<>();

        for (Resource resource : resources) {
            Scanner scanner = new Scanner(resource.getInputStream());
            while (scanner.hasNextLine()) {
                String line = scanner.nextLine().trim();
                Matcher matcher = pattern.matcher(line);
                if (matcher.find()) {
                    String tableName = matcher.group(1);
                    // 新增：排除excludeTables中的表
                    if (!excludeTables.contains(tableName)) {
                        tableNames.add(tableName);
                    }
                }
            }
        }

        return new ArrayList<>(tableNames);
    }
}
